EXEC xp_cmdshell 'net use M: \10.0.0.167\Transaccional px85947#@1/user:INABIMASD\administrador'EXEC xp_cmdshell 'net use T: \10.0.0.167\backups\MSSQL px85947#@1/user:INABIMASD\administrador'--exec xp_cmdshell 'net use Y: \127.0.0.1\Shared aVeRyStR0nGP@s$w0rd@123 /USER:Administrator /PERSISTENT:yes'--goexec xp_cmdshell 'net use'--exec xp_cmdshell 'net use T: /delete'

Crecimiento automático de los ficheros de la base de datos<a name="2"></a>

DBCC CHECKDB (Transact-SQL) <a name="5"></a>

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

Runs

DBCC CHECKALLOC

on the database.

Runs

DBCC CHECKTABLE

on every table and view in the database.

Runs

DBCC CHECKCATALOG

on the database.

Validates the contents of every indexed view in the database.

Validates link-level consistency between table metadata and file system directories and files when #### storing varbinary(max) data in the file system using FILESTREAM.

Validates the Service Broker data in the database.

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.

Information about this topychttps://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

Ejecucion de los dbcc check db de todas las bases de datos de Inabima de forma separada.Esto debe ser ejecutado con cuidado.

DBCC CHECKDB(N'AuditoriaDB') WITH NO_INFOMSGSGODBCC CHECKDB(N'Certificaciones') WITH NO_INFOMSGSGODBCC CHECKDB(N'DatosCertificaciones') WITH NO_INFOMSGSGODBCC CHECKDB(N'DatosPersonalesRRHH') WITH NO_INFOMSGSGODBCC CHECKDB(N'dbPadron2010') WITH NO_INFOMSGSGO

DBCC CHECKDB(N'DBTurismoMagisterial') WITH NO_INFOMSGSGODBCC CHECKDB(N'Genesis') WITH NO_INFOMSGSGODBCC CHECKDB(N'INABIMA') WITH NO_INFOMSGSGODBCC CHECKDB(N'INVENTARIO') WITH NO_INFOMSGSGODBCC CHECKDB(N'ManageEngineDB') WITH NO_INFOMSGSGODBCC CHECKDB(N'master') WITH NO_INFOMSGODBCC CHECKDB(N'MINERD') WITH NO_INFOMSGSGODBCC CHECKDB(N'model') WITH NO_INFOMSGSGODBCC CHECKDB(N'msdb') WITH NO_INFOMSGSGODBCC CHECKDB(N'Odontologico') WITH NO_INFOMSGSGODBCC CHECKDB(N'POA_INABIMA') WITH NO_INFOMSGSGODBCC CHECKDB(N'RegistroAfiliados') WITH NO_INFOMSGSGODBCC CHECKDB(N'SIGOB_INABIMA_RD') WITH NO_INFOMSGSGODBCC CHECKDB(N'TA100SQL') WITH NO_INFOMSGS

Nuestras bases de datos crecen con el uso y SQL Server reserva espacio en disco duro para los ficheros que la componen, pidiéndole al sistema operativo más espacio cuando el que tenía asignado deja de estar disponible. Cada vez que esto ocurre, el rendimiento de nuestra base de datos se ve afectado, ya que el servidor debe bloquear la actividad en ella mientras obtiene el nuevo espacio. Así pues, es deseable configurar las opciones de crecimiento de los ficheros de la base de datos de tal manera que:

-Los eventos de crecimiento ocurran con poca frecuencia

-No nos excedamos en la cantidad de disco reservada a los ficheros de una base de datos, ya que podría no ser necesario y estaríamos consumiendo recursos útiles para otras bases de datos en el mismo disco o sistema de discos.

/*Crecimiento de los mdb bases de datos..AJ 2018- noviembre 15*/SELECT[database_name] AS "database",format(backup_start_date, 'yyyy-MM-dd') as date,

--DATEPART(month,[backup_start_date]) AS "Month",AVG([backup_size]/1024/1024) AS "size MB"--AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",--AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"FROM msdb.dbo.backupset where [type] = 'D'and [database_name] = 'SIGOB_INABIMA_RD'GROUP BY [database_name], format(backup_start_date, 'yyyy-MM-dd') --DATEPART(mm,[backup_start_date]);order by date descUSE [AuditoriaDB]GOSELECT --[id] [DatabaseName] ,[Date] -- ,[logical_name] -- ,[Physical_name] ,[SizeMB] --,[SizeGB] -- ,[DateTime] FROM [dbo].[Db_SpaceMDB_log_inTime_Audit]where Physical_name = 'K:\MSSQL\MSSQLSERVER\DATA\SIGOB_INABIMA_RD.mdf' order by Date descGOdeclare @databasename varchar(50) = 'SIGOB_INABIMA_RD'SELECT[database_name] AS "database",format(backup_start_date, 'yyyy-MM-dd') as date,--DATEPART(month,[backup_start_date]) AS "Month",AVG([backup_size]/1024/1024) AS "size MB"--AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",--AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"FROM msdb.dbo.backupset where [type] = 'D' and database_name = @databasenameGROUP BY [database_name], format(backup_start_date, 'yyyy-MM-dd') --DATEPART(mm,[backup_start_date]);order by date desc

Tempdb: Reducir Tamaño <a name="6"></a>

La base de datos “Tempdb” es una base de datos de sistema la cual se utiliza para almacenar diversos objetos temporales, entre los mas conocidos estan las tablas temporales creadas explicitamente (#temptable), tambien tablas de trabajo generadas por los planes de ejecución para almacenar resultados intermedios, cursores materializados estaticos, y registros versionados cuando usamos alguno de los niveles de aislamiento de tipo

En este caso hay diversos metodos por los cuales reducirla:

Método 1: El más confiable de todos, pero requiere reiniciar el servicio de SQL Server. Cuando se reinicia el servicio de SQL Server la tempdb se recrea desde cero y sus archivos vuelven al tamaño original en el que fueron configurados por última vez.

Método 2: En el caso que no se tenga alguna ventana para el reinicio, entonces se puede utilizar este método el cual no requiere reiniciar el servicio de SQL Server, pero si algunos recursos del servidor para poder completarlo. Los pasos para este método son los siguientes:

-Verificar cuanto espacio disponible hay en la base de datos tempdb.

use tempdb;exec sp_spaceused

-Hacer “shrink” a la base de datos basado en ese espacio disponible. En este caso estoy reduciendo el archivo a un 10% de su tamaño actual

Método 3: Este método tampoco require reinicio del servicio de SQL Server y se utiliza cuando el anterior no produce el efecto deseado y es que hay ocaciones en las que no se puede reducir el tamaño de la tempdb debido a que aun contiene objetos activos tales como tablas de trabajo u otras estructuras temporales, entonces cuando tenemos este caso debemos hacer una serie de limpiezas en el motor antes de intentarlo. Es importante mencionar que esto deberia ser el ultimo recurso y pensarlo dos veces antes de ejecutarlo en producción ya que limpiara muchas cosas que el motor debera volver a calcular.

-Realizar la limpieza de varios cache y un checkpoint

USE tempdbGOCHECKPOINT;GODBCC DROPCLEANBUFFERS;GODBCC FREEPROCCACHE;GODBCC FREESYSTEMCACHE ('ALL');GODBCC FREESESSIONCACHE;GO

La siguiente acción no es necesariamente obligatoria, pero hay ocaciones en las que me ha servido, y es configurar el archivo de datos de la tempdb al tamaño deseado antes de darle el shrink. Para esto se debe ejecutar el siguiente comando ejemplo en el cual la estoy configurando a 100 MB:

use masterALTER DATABASE tempdbMODIFY FILE(name=tempdev,size=100M)

Finalmente reducir el tamaño del archivo de datos de la tempdb, en este caso lo reduzco a 100 MB

use tempdbdbcc shrinkfile ('tempdev',100,TRUNCATEONLY)

La conexión de administración dedicada: por qué la quiere, cuándo la necesita y cómo saber quién la está usando <a name="7"></a>

CAD? ¿Qué es eso?

-Primero, un poco de desambiguación. El acrónimo 'DAC' es demasiado popular.

-Para el contexto de este artículo, nuestra DAC es la 'Conexión de administración dedicada'.

-SQL Server también implementa una DAC totalmente no relacionada: una aplicación de nivel de datos o un paquete DAC. No estamos hablando de eso aquí.

¿Qué puede hacer DAC por usted?

¿Tiene un servidor SQL que está en crisis? El DAC puede ayudarlo a organizar una intervención.

La conexión de administración dedicada se creó para ayudarlo a conectarse y ejecutar consultas básicas de resolución de problemas en casos de problemas graves de rendimiento. Esta es su oportunidad para obtener una tarjeta de "Salir de la cárcel", pero como no la usa regularmente, es fácil olvidar cómo usarla. También es fácil olvidar habilitar el acceso al DAC de forma remota.

Cómo funciona la conexión de administración dedicada

El DAC utiliza un programador reservado especial que tiene un hilo para procesar solicitudes. Básicamente, esto significa que SQL Server mantiene una puerta trasera abierta a los recursos del procesador disponibles solo para usted.

No caigas en la tentación de abusar de este privilegio. Ese hilo es solo un hilo: no hay paralelismo para las consultas que se ejecutan en el DAC. El dos por ciento de ustedes tendrá la tentación de usar esto para sus trabajos de mantenimiento en sistemas ocupados. En serio, simplemente no vayas allí. El DAC no fue diseñado para un alto rendimiento.

Cómo habilitar el DAC para conexiones remotas y clústeres

De manera predeterminada, la DAC solo está habilitada para las cuentas registradas en la máquina local. Para servidores de producción, eso significa que solo funciona para sesiones de escritorio remoto en instancias de SQL Server no agrupadas. Si su instancia está agrupada o si se conecta a través de TCP/IP, no tendrá suerte a menos que cambie una configuración. Esa configuración es 'Conexiones de administración remota'.

"Si SQL Server no responde y la escucha de DAC no está habilitada, es posible que deba reiniciar SQL Server para conectarse con DAC."

¡Para agrupaciones, inscríbeme! Estoy a favor de habilitarlo para otras instancias también. En tiempos de problemas, desea minimizar la cantidad de tiempo que pasa usando el escritorio remoto en un servidor que tiene

problemas. Desea usar eso solo para recopilar información que no puede obtener de otra manera.

Habilitar el DAC para conexiones remotas es muy fácil. Está controlado por la configuración de 'Conexiones de administración remota'. Para habilitarlo, simplemente ejecute este fragmento de código:

Habilitar el DAC

EXEC sp_configure 'remote admin connections', 1; GO RECONFIGURE GO

Siempre hay una trampa. Es posible que también deba abrir los puertos del firewall, según su entorno y desde dónde desee conectarse. Probablemente será el puerto 1434, pero variará según su configuración. (Books Online lo respalda: lea más en la sección "Puerto DAC" aquí).

Solo un administrador del sistema puede montar este caballo a la vez

Esta no es una línea compartida, solo un administrador de sistemas a la vez puede usar el DAC. Además, solo debe ejecutar consultas simples y rápidas utilizando el DAC.

En otras palabras, solo conéctese al DAC cuando realmente lo necesite. Cuando haya terminado, limpie el asiento, asegúrese de desconectarlo.

Cómo conectarse al DAC

Puede conectarse al DAC usando la línea de comando. Utilice la opción "-A" con SQLCMD.exe.

Me resulta más conveniente conectarme en Management Studio. Para ello, coloque el prefijo "Admin:" en el nombre de la instancia a la que se está conectando.

Un FYI: Object Explorer no puede conectarse al DAC. Si abre SSMS y tiene el Explorador de objetos conectado de forma predeterminada, el primer aviso de conexión que verá será para eso. Si intenta decirle que se conecte al DAC, fallará. Eso es algo bueno, no queremos que el poder se le suba a la cabeza al Explorador de objetos.

¿Quién ha estado durmiendo en mi DAC? Cómo saber quién está usando la conexión de administración dedicada.

Si intenta conectarse al DAC cuando alguien ya está conectado, verá un error de conexión. Probablemente no le dirá directamente que alguien ya está conectado al DAC, pero si revisa el registro de errores de SQL Server, debería ver el mensaje:

No se pudo conectar porque ya existe el número máximo de conexiones de administrador dedicadas '1'. Antes de que se pueda realizar una nueva conexión, se debe eliminar la conexión de administrador dedicada existente, ya sea cerrando la sesión o finalizando el proceso.

SELECTCASEWHEN ses.session_id= @@SPID THEN 'It''s me! 'ELSE '' END

+coalesce(ses.login_name,'???') as WhosGotTheDAC,ses.session_id,ses.login_time,ses.status,ses.original_login_namefrom sys.endpoints as enjoin sys.dm_exec_sessions ses onen.endpoint_id=ses.endpoint_idwhere en.name='Dedicated Admin Connection'

## Cómo mover TempDB a otra unidad y carpeta<a name="2_1"><a/>![](https://www.dotnetcr.com/wp-content/uploads/2018/05/sql-mover-tempdb.png)~~~sqlSELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'+ ' FILENAME = ''Z:\MSSQL\DATA' + f.name+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END+ ''');'FROM sys.master_files fWHERE f.database_id = DB_ID(N'tempdb');

El Resultado de esta consulta seria lo siguiente.

al ejecutar este resultado en la consola de Mssql server se modificaran los temporales de las bases de datos.

MEJORES PRÁCTICAS EN TEMPDB DE SQL SERVER

Primero, ¿por qué es tan importante?

La tempdb es un recurso global dentro de nuestra instancia. Todas las conexiones pueden utilizarla.

Imagínate una conexión que necesite de ella y la base no esté disponible. Sería un desastre, considerando su naturaleza.

¿Pero qué contiene?

Puede almacenar diferentes objetos o ser un soporte a diferentes procesos. Almacena tablas o procedimientos temporales, resultados temporales de funciones o cursores como también soporta a procesos internos como spools, sorts, group by, order by, union o también ayuda en procesos de ordenamientos de índices cuando se especifica el valor SORT_IN_TEMPDB en su creación o reconstrucción.

Básicamente podríamos decir que la tempdb está presente en todo lo que podemos hacer en nuestra instancia. Vamos entendiendo su criticidad, ¿verdad?

Ahora bien, los factores que pueden costarnos caro. ¡Evítalos!

1 Tempdb compartiendo disco lógico

Si tienes la base de datos tempdb en una unidad de disco junto con otras bases de datos de sistema o de usuario, nunca podrás tener un seguimiento aislado del performance o de su demanda en espacio físico.

Por otro lado, harás más difícil el monitoreo y mantenimiento pues ya no podrás controlar el crecimiento individual mirando solo la unidad del disco.

2 Autocrecimiento por defecto

Dependiendo de la versión que tengas de SQL Server o si cambiaste las configuraciones por defecto en la instalación, el valor del crecimiento automático puede variar.

El problema surje cuando el valor es muy pequeño y empezamos a sentir contención por la necesidad de expansión de la base de datos. Imagina que el crecimiento se da de 1 MB en 1 MB y necesitamos crecer 1 GB…necesitamos “hacer ese crecimiento” 1000 veces. No es lo mismo que

crecer dos veces en un ritmo de 500 MB en 500 MB. Mientras más pequeña la tasa, más esfuerzo en escritura en disco.

3 1 solo archivo de datos

Las bases de datos tienen archivos físicos de datos (mdf, ndf) y de log (ldf). Por defecto cada base tiene un archivo de data y uno de log.

Se ha visto que existen escenarios de lentitud de servidores en los que se genera una contención particular en la base de datos tempdb. Usualmente esta se ve como tiempos de espera PAGELATCH_UP y pueden ser referencias a páginas del formato 2:1:1, 2:1:3 (PFS, SGAM). Esta contención se puede liberar fácilmente cuando se piensa en Dividir Archivos de data en tempdb.

4 Algunos otros

Tener collation diferentes entre la base de datos tempdb, la instancia de SQL Server y/o otras bases de usuario. Nos puede llevar a generar errores de collate en JOINS.

Crecimiento disparejo en archivos de datos. Puede restar optimización en la distribución de páginas hacia los archivos.

No monitorear la base de datos tempdb. Fácilmente puede llevarnos a perder el acceso a la instancia a través de errores por falta de espacio en esta base. Ya te pasó, ¿verdad? Ya recordaste las letras rojas diciendo “The transaction log for database tempdb is full”.

(Muy típico y usual) Pensar que su espacio es infinito. Muchas prácticas en desarrollo o administración de SQL Server malinterpretan el uso de la base tempdb como “espacio temporal”, esto no es un espacio ilimitado o que “solo está en memoria” como a veces se piensa. Mientras utilices tablas temporales en una conexión abierta, con esta ejecución puedes ver un reporte de tamaños de objetos temporales.

Ya lo sabes, no es un espacio infinito. Cuidado con las operaciones de DBCC CHECKDB, INDEX REBUILD con SORT_IN_TEMPDB, cursores o consultas gigantescas con ORDER BY en campos sin índices. Siempre monitorea tus mejores prácticas en tempdb.

Eliminar corroes del servidor de correos Sql Server<a name="3"><a/>

Observaciones

Correo electrónico de base de datos mensajes y sus datos adjuntos se almacenan en la base de datos msdb . Los mensajes deben eliminarse periódicamente para evitar que msdb crezca más de lo esperado y cumplir con el programa de retención de documentos de las organizaciones. Utilice el sysmail_delete_mailitems_sp procedimiento almacenado para eliminar de forma permanente los mensajes de correo electrónico de las tablas de correo electrónico de base de datos. Un argumento opcional permite eliminar solo los mensajes de correo electrónico antiguos indicando la fecha y la hora. Se eliminarán los mensajes de correo electrónico anteriores al valor de ese argumento. Otro argumento opcional permite eliminar solo los mensajes de correo electrónico de un tipo determinado, especificado como el argumento sent_status . Debe proporcionar un argumento para ** @ sent_before** o ** @ sent_status**. Para eliminar todos los mensajes, utilice ** @ sent_before = getDate ()**.

Si se eliminan mensajes de correo electrónico, se eliminarán también los archivos adjuntos relacionados con los mismos. Al eliminar el correo electrónico, no se eliminan las entradas correspondientes en sysmail_event_log. Use sysmail_delete_log_sp para eliminar elementos del registro.

Permisos

De forma predeterminada, este procedimiento almacenado se concede para la ejecución a los miembros del rol fijo de servidor sysadmin y de DatabaseMailUserRole. Los miembros del rol fijo de servidor sysadmin pueden ejecutar este procedimiento para eliminar los mensajes de correo electrónico enviados por todos los usuarios. Los miembros de DatabaseMailUserRole solo pueden eliminar los mensajes de correo electrónico enviados por ese usuario.

Ejemplos

DECLARE @GETDATE datetime SET @GETDATE = GETDATE(); EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE; GO USE msdb ; GO

EXECUTE dbo.sysmail_start_sp ; GO

De ser necesario para corregir el problema procederemos a ejecutar el siguiente archivo.

Dependiendo de tu instalacion y/o Sistema Operativo puede estar en una Direccion u otra. Pero la direccion seria similar a la mostrada a continuacion.

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe

Conexiones Activas del Servidor de SQL SERVER<a name="4"></a>

A veces es necesario conocer las conexiones activas en una instancia de Microsoft SQL Server.

Para obtener esta información yo suelo usar la siguiente consulta SQL que devuelve el servidor,

la base de datos, el usuario, el número de conexiones y la marca temporal de cuando se ejecuto la consulta.

/* Alejandro Jimenez 2019-08-01*/SELECT @@ServerName AS server, NAME AS dbname,

LOGINAME AS LoginName, COUNT(STATUS) AS number_of_connections, GETDATE() AS timestampFROM sys.databases sdLEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbidWHERE database_id NOT BETWEEN 1 AND 4AND LOGINAME IS NOT NULLGROUP BY NAME,LOGINAME;

Una vista dentro de la caché del búfer de SQL Server<a name="8"></a>

Datos de Manejo de Memoria y paginacion en el Servidor sql server

Alejandro Jimenez Febrero 5 del 2021

original December 29, 2016 by Ed Pollack

Cuando hablamos acerca del uso de la memoria en SQL Server, a menudo nos referimos a la caché del búfer. Esta es una parte importante de la arquitectura de SQL Server, y es responsable por la habilidad de consultar datos frecuentemente accedidos extremadamente rápido. Saber cómo funciona la caché del búfer nos permitirá asignar apropiadamente memoria en SQL Server, estimar de manera precisa cómo las bases de datos están accediendo los datos, y asegura que no haya ineficiencias en nuestro código que causen que datos excesivos sean enviados a la caché.

¿Qué hay en la caché del búfer?<a name="quehayenlacachedelbufer"></a>

Los discos duros son lentos, la memoria es rápida. Este es un hecho de la naturaliza para cualquiera que trabaja con computadoras. Incluso los SSDs son lentos en comparación a la memoria de alto rendimiento. La manera en que el software lidia con este problema es escribir los datos desde el

almacenamiento lento a la memoria rápida. Una vez cargadas, sus aplicaciones favoritas pueden desempeñarse muy rápidamente y sólo necesitan volver al disco cuando nuevos datos son necesarios. Este hecho en la computación es una parte importante de la arquitectura de SQL Server.

Cuando sea que los datos son escritos a o leídos desde una base de datos SQL Server, serán copiados a la memoria por el administrador del búfer. La caché del búfer (también conocida como grupo de búferes) usará tanta memoria como esté asignada a ella para mantener tantas páginas de datos como sea posible. Cuando la caché del búfer se llena, datos más antiguos y menos usados serán purgados para hacer campo para datos más nuevos.

Los datos son almacenados en páginas de 8k dentro de la caché del búfer y pueden ser referidos como páginas “limpias” o “sucias”. Una página sucia es una que ha sido cambiada desde la última vez que fue escriba al disco y es el resultado de una operación de escritura contra el índice o los datos de tabla. Las páginas limpias son aquellas que no han cambiado, y los datos dentro de ellas aún coinciden con lo que está en el disco. Los puntos de control son publicados automáticamente en el fondo por SQL Server y escribirán páginas sucias al disco para crear un buen punto conocido de restauración en el evento de un colapso u otra situación desafortunada del servidor.

Usted puede ver una vista general del estado actual del uso de la memoria en SQL Server revisando la DMV sys.dm_os_sys_info DMV:<a name="8-1"></a>

SELECTphysical_memory_kb,virtual_memory_kb,committed_kb,committed_target_kbFROM sys.dm_os_sys_info;

Los resultados de esta consulta me dicen algo acerca del uso de la memoria en mi servidor:

Aquí está lo que significan las columnas:

physical_memory_kb: Memoria física total instalada en el servidor.

virtual_memory_kb: Cantidad total de memoria virtual disponible para SQL Server. Idealmente, no queremos utilizar esto tan a menudo como la memoria virtual (usar un archivo de página en el disco o en algún otro lugar que no sea la memoria, va a ser significativamente más lento que la memoria).

Committed_kb: La cantidad de memoria actualmente asignada por la caché del búfer para el uso por páginas de base de datos.

Committed_target_kb: Esta es la cantidad de memoria que la caché del búfer “quiere” usar. Si la cantidad actualmente en uso (indicada por commited_kb) es más alta que esta cantidad, entonces el administrador del búfer comenzará a remover páginas antiguas desde la memoria. Si la cantidad actualmente en uso es menor, entonces el administrador del búfer asignará más memoria para nuestros datos.

EL uso de la memoria es crítico para el desempeño de SQL Server – si no hay suficiente memoria disponible para servir nuestras consultas comunes, entonces usaremos muchos más recursos leyendo datos desde el disco, sólo para descartarlos y leerlos de nuevo después.

¿Cómo podemos usar las métricas de la caché del búfer?

Nosotros podemos acceder a la información acerca de la caché del búfer usando la vista dinámica de administración sys.dm_os_buffer_descriptors, la cual provee todo lo que usted siempre quiso saber acerca de los datos almacenados en la memoria por SQL Server, pero temía preguntar. Dentro de esta vista, usted encontrará una sola fila por descriptor de búfer, lo que da identificación única y provee algo de información acerca de cada página en la memoria. Note que, en un servidor con bases de datos grandes, puede tomar un poco de tiempo consultar esta vista.

Una métrica útil que es fácil de obtener es la medida del uso de la caché del búfer por la base de datos en el servidor:<a name="metricausocachequery"></a>

SELECT databases.name AS database_name, COUNT(*) * 8 / 1024 AS mb_usedFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.databasesON databases.database_id = dm_os_buffer_descriptors.database_idGROUP BY databases.nameORDER BY COUNT(*) DESC;

Esta consulta retorna, ordenada desde más páginas a menos, la cantidad de memoria consumida por cada base de datos en la caché del búfer:

Mi servidor local no es increíblemente emocionante ahora…pero si decidiéramos correr una variedad de consultas contra AdventureWorks2014, podríamos correr nuestra consulta desde arriba de nuevo para verificar el impacto que tuvo en la caché del búfer:

Mientras que no enloquecí aquí, mi consulta al azar sí incrementó la cantidad de datos en la caché del búfer para AdventureWorks2014 en 27MB. Esta consulta puede ser una manera útil de determinar rápidamente qué base de datos cuenta con el mayor uso de memoria en la caché del búfer. En una arquitectura multiusuario, o en un servidor en el cual hay muchas bases de datos clave compartiendo recursos, este puede ser un método rápido para hallar la base de datos que está desempeñándose pobremente o acaparando la memoria en cualquier momento.

De forma similar, podemos ver los totales como una página o un conteo de bytes:<a name="totalespaginasconteo"></a>

SELECTCOUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptors;

Esto retorna una sola fila conteniendo el número de páginas en la caché del búfer, así como la memoria consumida por ellas:

Dado que una página es de 8KB, podemos convertir el número de páginas en megabytes multiplicando por 8 para obtener KB, y luego dividir por 1024 para llegar a MB.

Podemos subdividir esto más allá y ver cómo la caché del búfer es usada por objetos específicos. Esto puede proveer mucha más información acerca del uso de la memoria, ya que podemos determinar qué tablas son acaparadoras de memoria. Adicionalmente, podemos verificar algunas métricas interesantes, como qué porcentaje de una tabla está en la memoria actualmente, o qué tablas son infrecuentemente usadas (o no). La siguiente consulta retornará las páginas en búfer y el tamaño por tabla:

SELECTobjects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY objects.name, objects.type_descORDER BY COUNT(*) DESC;

Las tablas de sistema son excluidas, y esto sólo jalará datos para la base de datos actual. Las vistas indexadas no serán incluidas, ya que sus índices son entidades distintas de las tablas de las que derivan- La combinación en sys.partitions contiene dos partes para manejar los índices, así como las pilas. Los datos mostrados aquí incluyen todos los índices en la tabla, así como la pila, si no hay ninguna definida.

Un segmento de los resultados de esto es el siguiente (para AdventureWorks2014):

De forma similar, podemos dividir los datos por índice en lugar de por tabla, proveyendo incluso más granularidad en el uso de la caché del búfer:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idINNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY COUNT(*) DESC;

Esta consulta es casi la misma que nuestra última, excepto que hacemos una combinación adicional a sys.indexes, y agrupamos en el nombre del índice en adición al nombre de la tabla/vista. Los resultados proveen incluso más detalles acerca de cómo la caché del búfer está siendo usada, y pueden ser valiosos en tablas con muchos índices de uso variado:

Los resultados pueden ser útiles cuando se trata de determinar el nivel general de uso para un índice específico en cualquier momento. Adicionalmente, nos permite estimar cuánto de un índice está siendo leído, comparado con su tamaño general.

Para recolectar el porcentaje de cada tabla que está en la memoria, podemos poner esa consulta en un CTE y comparar las páginas en memoria versus el total para cada tabla:

WITH CTE_BUFFER_CACHE AS (SELECTobjects.name AS object_name,objects.type_desc AS object_type_description,objects.object_id,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY objects.name, objects.type_desc, objects.object_id)SELECTPARTITION_STATS.name,CTE_BUFFER_CACHE.object_type_description,CTE_BUFFER_CACHE.buffer_cache_pages,CTE_BUFFER_CACHE.buffer_cache_used_MB,PARTITION_STATS.total_number_of_used_pages,PARTITION_STATS.total_number_of_used_pages * 8 / 1024 AS total_mb_used_by_object,CAST((CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) * 100) AS DECIMAL(5,2)) AS

percent_of_pages_in_memoryFROM CTE_BUFFER_CACHEINNER JOIN (SELECT objects.name,objects.object_id,SUM(used_page_count) AS total_number_of_used_pagesFROM sys.dm_db_partition_statsINNER JOIN sys.objectsON objects.object_id = dm_db_partition_stats.object_idWHERE objects.is_ms_shipped = 0GROUP BY objects.name, objects.object_id) PARTITION_STATSON PARTITION_STATS.object_id = CTE_BUFFER_CACHE.object_idORDER BY CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) DESC;

Esta consulta combina nuestro conjunto previo de datos con una consulta en sys.dm_db_partition_stats para comparar lo que está actualmente en la caché del búfer versus el espacio total usado por cualquier tabla dada. Las muchas operaciones CAST al final ayudan a evitar el truncado y hacen al resultado final fácil de leer. Los resultados en mi servidor local son los siguientes:

Estos datos nos dicen qué tablas están las zonas calientes en nuestra base de datos, y con un poco de conocimiento de su uso de aplicación, podemos determinar cuáles simplemente tienen demasiados datos residiendo en la memoria. Las tablas pequeñas son probablemente no muy importantes para nosotros aquí. Por ejemplo, las primeras cuatro en la salida anterior están debajo de un megabyte y si quisiéramos omitirlas, podríamos filtrar los resultados para retornar solamente tablas más grandes que un tamaño específico de interés.

Por otra parte, estos datos nos dicen que ¾ de SalesOrderDetail está en la caché del búfer. Si esto pareciera inusual, consultaría la caché del plan de consultas y determinaría si hay alguna consulta ineficiente en la tabla que está seleccionando *, o una cantidad excesivamente grande de datos. Combinando nuestras métricas desde la caché del búfer y la caché del plan, podemos idear nuevas maneras de determinar con precisión mañas consultas o aplicaciones que están jalando muchos más datos de lo que requieren.

Esta consulta puede ser modificada para proveer el porcentaje de un índice que está siendo usado también, de forma similar a cómo recolectamos el porcentaje de una tabla usada:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB,SUM(allocation_units.used_pages) AS pages_in_index,SUM(allocation_units.used_pages) * 8 /1024 AS total_index_size_MB,CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memory

FROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idINNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) DESC;

Dado que sys.allocation_units provee algo de información acerca del tamaño de nuestros índices, evitamos la necesidad de CTEs y conjuntos de datos adicionales de dm_db_partition_stats. Aquí está un pedazo de los resultados, mostrando el tamaño del índice (MB y páginas) y el espacio usado de la caché del búfer (MB y páginas):

Si estuviéramos poco interesados en tablas/índices pequeños, podríamos añadir una cláusula HAVING a la consulta para filtrar por un índice que es más pequeño que un tamaño especificado, en MB o páginas. Estos datos proveen una vista de la eficiencia de las consultas en índices específicos y podría asistir en la limpieza de índices, el ajuste de índices o algún ajuste más granular del uso de la memoria en su SQL Server.

Una columna interesante en dm_os_buffer_descriptors es free_space_in_bytes. Esta columna nos dice cuán llena está cada página en la caché del búfer, y por lo tanto provee un indicador del espacio potencial desperdiciado o la ineficiencia. Podemos determinar el porcentaje de páginas que han sido tomadas por el espacio libre, en lugar de datos, para cada base de datos en nuestro servidor:

WITH CTE_BUFFER_CACHE AS( SELECT databases.name AS database_name,

COUNT(*) AS total_number_of_used_pages, CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB, CAST(CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / (1024 * 1024) AS DECIMAL(20,2)) AS buffer_cache_free_space_in_MB FROM sys.dm_os_buffer_descriptors INNER JOIN sys.databases ON databases.database_id = dm_os_buffer_descriptors.database_id GROUP BY databases.name)SELECT *, CAST((buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0)) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_spaceFROM CTE_BUFFER_CACHEORDER BY buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0) DESC

Esto retorna una fila por base de datos que muestra la agregación de espacio libre por base de datos, sumada a través de todas las páginas en la caché del búfer para esa base de datos particular:

Esto es interesante, pero no muy útil todavía debido a que estos resultados no son muy específicos. Nos dicen que una base de datos puede tener un poco de espacio desperdiciado, pero no mucho sobre qué tablas son las causantes. Tomemos el mismo enfoque que hicimos anteriormente y devolvemos espacio libre por tabla en una base de datos dada:

De forma similar, podemos dividir los datos por índice en lugar de por tabla, proveyendo incluso más granularidad en el uso de la caché del búfer:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_id

INNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY COUNT(*) DESC;

Esta consulta es casi la misma que nuestra última, excepto que hacemos una combinación adicional a sys.indexes, y agrupamos en el nombre del índice en adición al nombre de la tabla/vista. Los resultados proveen incluso más detalles acerca de cómo la caché del búfer está siendo usada, y pueden ser valiosos en tablas con muchos índices de uso variado:

Los resultados pueden ser útiles cuando se trata de determinar el nivel general de uso para un índice específico en cualquier momento. Adicionalmente, nos permite estimar cuánto de un índice está siendo leído, comparado con su tamaño general.

Para recolectar el porcentaje de cada tabla que está en la memoria, podemos poner esa consulta en un CTE y comparar las páginas en memoria versus el total para cada tabla:<a name="percentajecadapagina"></a>

WITH CTE_BUFFER_CACHE AS (SELECTobjects.name AS object_name,objects.type_desc AS object_type_description,objects.object_id,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objects

ON partitions.object_id = objects.object_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY objects.name, objects.type_desc, objects.object_id)SELECTPARTITION_STATS.name,CTE_BUFFER_CACHE.object_type_description,CTE_BUFFER_CACHE.buffer_cache_pages,CTE_BUFFER_CACHE.buffer_cache_used_MB,PARTITION_STATS.total_number_of_used_pages,PARTITION_STATS.total_number_of_used_pages * 8 / 1024 AS total_mb_used_by_object,CAST((CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memoryFROM CTE_BUFFER_CACHEINNER JOIN (SELECT objects.name,objects.object_id,SUM(used_page_count) AS total_number_of_used_pagesFROM sys.dm_db_partition_statsINNER JOIN sys.objectsON objects.object_id = dm_db_partition_stats.object_idWHERE objects.is_ms_shipped = 0GROUP BY objects.name, objects.object_id) PARTITION_STATSON PARTITION_STATS.object_id = CTE_BUFFER_CACHE.object_idORDER BY CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) DESC;

Esta consulta combina nuestro conjunto previo de datos con una consulta en sys.dm_db_partition_stats para comparar lo que está actualmente en la caché del búfer versus el espacio total usado por cualquier tabla dada. Las muchas operaciones CAST al final ayudan a evitar el truncado y hacen al resultado final fácil de leer. Los resultados en mi servidor local son los siguientes:

Estos datos nos dicen qué tablas están las zonas calientes en nuestra base de datos, y con un poco de conocimiento de su uso de aplicación, podemos determinar cuáles simplemente tienen demasiados datos residiendo en la memoria. Las tablas pequeñas son probablemente no muy importantes para nosotros aquí. Por ejemplo, las primeras cuatro en la salida anterior están debajo de un megabyte y si quisiéramos omitirlas, podríamos filtrar

los resultados para retornar solamente tablas más grandes que un tamaño específico de interés.

Por otra parte, estos datos nos dicen que ¾ de SalesOrderDetail está en la caché del búfer. Si esto pareciera inusual, consultaría la caché del plan de consultas y determinaría si hay alguna consulta ineficiente en la tabla que está seleccionando *, o una cantidad excesivamente grande de datos. Combinando nuestras métricas desde la caché del búfer y la caché del plan, podemos idear nuevas maneras de determinar con precisión mañas consultas o aplicaciones que están jalando muchos más datos de lo que requieren.

Esta consulta puede ser modificada para proveer el porcentaje de un índice que está siendo usado también, de forma similar a cómo recolectamos el porcentaje de una tabla usada:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB,SUM(allocation_units.used_pages) AS pages_in_index,SUM(allocation_units.used_pages) * 8 /1024 AS total_index_size_MB,CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memoryFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idINNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) DESC;

Dado que sys.allocation_units provee algo de información acerca del tamaño de nuestros índices, evitamos la necesidad de CTEs y conjuntos

de datos adicionales de dm_db_partition_stats. Aquí está un pedazo de los resultados, mostrando el tamaño del índice (MB y páginas) y el espacio usado de la caché del búfer (MB y páginas):

Si estuviéramos poco interesados en tablas/índices pequeños, podríamos añadir una cláusula HAVING a la consulta para filtrar por un índice que es más pequeño que un tamaño especificado, en MB o páginas. Estos datos proveen una vista de la eficiencia de las consultas en índices específicos y podría asistir en la limpieza de índices, el ajuste de índices o algún ajuste más granular del uso de la memoria en su SQL Server.

Una columna interesante en dm_os_buffer_descriptors es free_space_in_bytes. Esta columna nos dice cuán llena está cada página en la caché del búfer, y por lo tanto provee un indicador del espacio potencial desperdiciado o la ineficiencia. Podemos determinar el porcentaje de páginas que han sido tomadas por el espacio libre, en lugar de datos, para cada base de datos en nuestro servidor:

WITH CTE_BUFFER_CACHE AS( SELECT databases.name AS database_name, COUNT(*) AS total_number_of_used_pages, CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB, CAST(CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / (1024 * 1024) AS DECIMAL(20,2)) AS buffer_cache_free_space_in_MB FROM sys.dm_os_buffer_descriptors INNER JOIN sys.databases ON databases.database_id = dm_os_buffer_descriptors.database_id GROUP BY databases.name)SELECT *, CAST((buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0)) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_spaceFROM CTE_BUFFER_CACHEORDER BY buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0) DESC

Esto retorna una fila por base de datos que muestra la agregación de espacio libre por base de datos, sumada a través de todas las páginas en la caché del búfer para esa base de datos particular:

Esto es interesante, pero no muy útil todavía debido a que estos resultados no son muy específicos. Nos dicen que una base de datos puede tener un poco de espacio desperdiciado, pero no mucho sobre qué tablas son las causantes. Tomemos el mismo enfoque que hicimos anteriormente y devolvemos espacio libre por tabla en una base de datos dada:

SELECTobjects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB,CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / 1024 / 1024 AS buffer_cache_free_space_in_MB,CAST((CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / 1024 / 1024) / (CAST(COUNT(*) * 8 AS DECIMAL) / 1024) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_spaceFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY objects.name,objects.type_desc,objects.object_idHAVING COUNT(*) > 0ORDER BY COUNT(*) DESC;

Esto devuelve una fila por tabla o vista indexada que tiene al menos una página en la memoria caché del búfer ordenada primeramente por aquellos que tienen la mayor cantidad de páginas en memoria.

¿Qué significa exactamente? Cuanto más espacio libre por página existe en promedio, más páginas hay que leer para retornar los datos que estamos buscando. Además, se requieren más páginas para almacenar datos, lo que significa que se necesita más espacio en la memoria y en

disco para mantener nuestros datos. El espacio perdido también significa más E/S para obtener los datos que necesitamos y las consultas se ejecutan más tiempo de lo necesario a medida que se recuperan estos datos.

La causa más común de un exceso de espacio libre son las tablas con filas muy amplias. Puesto que una página es de 8k, si una fila pasó a ser de 5k, nunca seríamos capaces de encajar una sola fila en una página, y siempre habrá ese extra de ~3k de espacio libre que no se puede utilizar. Las tablas con muchas operaciones de inserción aleatoria pueden ser problemáticas también. Por ejemplo, una clave que no aumenta puede resultar en divisiones de página cuando los datos se escriben en diferente orden. Un GUID sería el peor de los casos, pero cualquier clave que no está aumentando de manera natural puede dar lugar a este problema hasta cierto punto.

A medida que los índices se fragmentan con el tiempo, la fragmentación se verá en parte como un exceso de espacio libre cuando observamos el contenido de la caché del búfer. La mayoría de estos problemas se resuelven con un diseño inteligente de bases de datos y un mantenimiento razonable de la base de datos. Este no es el lugar para entrar en detalles sobre esos temas, pero hay muchos artículos y presentaciones sobre estos temas en la red para su entretenimiento.

Al principio de este artículo, discutimos brevemente qué páginas sucias y limpias son y su correlación con las operaciones de escritura dentro de una base de datos. Dentro de dm_os_buffer_descriptors podemos verificar si una página está limpia o no está usando la columna is_modified. Esta nos dice si una página ha sido modificada por una operación de escritura, pero aún no se ha escrito en disco. Podemos usar esta información para contar las páginas limpias y sucias en la caché del búfer para una base de datos determinada:

SELECT databases.name AS database_name,COUNT(*) AS buffer_cache_total_pages, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 1ELSE 0END) AS buffer_cache_dirty_pages, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 0

ELSE 1END) AS buffer_cache_clean_pages, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 1ELSE 0END) * 8 / 1024 AS buffer_cache_dirty_page_MB, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 0ELSE 1END) * 8 / 1024 AS buffer_cache_clean_page_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.databasesON dm_os_buffer_descriptors.database_id = databases.database_idGROUP BY databases.name;

Esta consulta devuelve el número de páginas y el tamaño de los datos en MB:<a name="numerodepaginas"></a>

Mi servidor no tiene mucho por el momento. Si corriera una gran sentencia de actualización, podríamos ilustrar qué veríamos cuando más operaciones de escritura están ocurriendo. Corramos la siguiente consulta:

UPDATE Sales.SalesOrderDetailSET OrderQty = OrderQty

Esto es esencialmente una no-operación y no resultará en ningún cambio real a la tabla SalesOrderDetail – pero SQL Server aún pasará por el problema de actualizar cada fila en la tabla para esta columna particular. Si corremos el conteo de páginas sucias/limpias desde arriba, obtendremos algunos resultados interesantes:

Cerca de 2/3 de las páginas para AdventureWorks2014 en la caché del búfer están sucios. Adicionalmente, TempDB también tiene bastante actividad, lo cual es indicativo del desencadenador update/insert/delete en la tabla, lo que causó que se ejecutara una gran cantidad de T-SQL adicional. El desencadenador causó que haya bastantes lecturas contra AdventureWorks2014, así como la necesidad de trabajo de tablas en TempDB para procesar esas operaciones adicionales.

Como antes, podemos dividir esta tabla o índice para recolectar datos más granulares acerca del uso de la caché del búfer:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_total_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 1ELSE 0END) AS buffer_cache_dirty_pages, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 0ELSE 1END) AS buffer_cache_clean_pages, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 1ELSE 0END) * 8 / 1024 AS buffer_cache_dirty_page_MB, SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1THEN 0ELSE 1END) * 8 / 1024 AS buffer_cache_clean_page_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idINNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY COUNT(*) DESC;

Los resultados muestran el uso de la caché del búfer por índice, mostrando cuántas páginas en la memoria están limpias o sucias:

Estos datos proveen una idea de la actividad de escritura en un índice dado en este punto del tiempo. Si fuera rastrado en un periodo de días o semanas, podríamos comenzar a estimar la actividad de escritura general del índice y proyectar una tendencia. Esta investigación podría ser útil si usted estuviera buscando entender el mejor nivel posible de aislamiento para usarse en una base de datos, o si esos reportes que siempre son corridos con READ UNCOMMITED podrían ser más susceptibles a lecturas sucias que lo pensado originalmente. En este caso específico, las páginas sucias todas se relacionan con la consulta de actualización que corrimos previamente, y por lo tanto abarcan un conjunto algo limitado.

DBCC DROPCLEANBUFFERS<a name="dbccdropcleanbuffers"></a>

Un comando DBCC que es a menudo usado como una forma de probar una consulta y estimar de forma precisa la velocidad de ejecución, es DBCC DROPCLEANBUFFERS. Cuando se corre, este comando remueve todas las páginas limpias de la memoria para un servidor entero de base de datos, dejando atrás solamente páginas sucias, las cuales serán típicamente una pequeña minoría de datos.

DBCC DROPCLEANBUFFERS es un comando que típicamente debería ser corrido sólo en un ambiente que no sea de producción, e incluso entonces, sólo cuando no se está realizando pruebas de desempeño o carga. El resultado de este comando es que la caché del búfer terminará mayormente vacía. Cualquier consulta corrida después de este punto necesitará usar lecturas físicas para traer de vuelta los datos a la caché desde su sistema de almacenamiento, lo cual es, como dijimos antes, mucho más lento que la memoria.

Después de correr este comando en mi servidor local, la consulta de páginas sucias/impías de más antes retorna lo siguiente:

¡Eso es todo lo que queda! Repitiendo mi aviso anterior: Trate este comando de manera similar a DBCC FREEPROCCACHEI en que no debería ser corrido en ningún servidor de producción a menos que usted esté absolutamente seguro de lo que hace.

Esta puede ser una herramienta útil de desarrollo debido a que usted puede correr una consulta en un ambiente de pruebas de desempeño una y otra vez sin ningún cambio en la velocidad/eficiencia debido a que se está enviando los datos de la memoria a la caché. Elimine los datos limpios del búfer entre ejecuciones y estará listo. Esto puede proveer resultados engañosos, aunque en esos ambiente de producción siempre usarán la caché del búfer, y no leerán desde su sistema de almacenamiento a menos que sea necesario. Eliminar los búferes limpios llevará a tiempos de ejecución más lentos que lo que se vería de otra forma, pero puede proveer una manera de probar las consultas en un ambiente consistente con cada ejecución.

Entendiendo todas estas advertencias, siéntase libre de usar esto como lo necesite para probar y obtener una visión acerca del desempeño de las consultas, las páginas leídas en la memoria como resultado de una

consulta, las páginas sucias creadas por una sentencia de escritura, y así por el estilo.

Expectativa de Vida de las Páginas<a name="expectativadevidadelaspaginas"></a>

Cuando se discute acerca del desempeño de la memoria en SQL Server, es poco probable que avancemos unos minutos antes de que alguien pregunte acerca de la expectativa de vida de las páginas (PLE, por sus siglas en inglés). PLE es una medida de, en promedio, cuánto tiempo (en segundos) permanecerá una página en la memoria sin ser accedida, punto después del cual es removida. Esta es una métrica que deseamos que sea más alta en la medida que deseamos que nuestros datos importantes permanezcan en la caché del búfer por tanto tiempo como sea posible. Cuando la PLE se ralentiza, los datos están siendo constantemente leídos desde el disco (alias ‘lento’) a la caché del búfer, removidos desde la caché y probablemente leídos desde el disco de nuevo en un futuro cercano. ¡Esta es la receta para un SQL Server lento (y frustrante)!

Para ver la PLE actual en un servidor, usted puede correr la siguiente consulta, la cual pondrá el valor actual desde la vista de administración dinámica de conteo de desempeño:

SELECT*FROM sys.dm_os_performance_countersWHERE dm_os_performance_counters.object_name LIKE '%Buffer Manager%'AND dm_os_performance_counters.counter_name = 'Page life expectancy';

Los resultados se ven así:

cntr_value es el valor del contador de desempeño, y en mi silencioso servidor local es 210,275 segundos. Dado que muy pocos datos son leídos o escritos en mi SQL Server, la necesidad de remover datos desde la caché del búfer es baja, y por tanto la PLE es absurdamente alta. En un servidor de producción altamente usado, la PLE sería casi con seguridad más baja.

Si su servidor tiene una arquitectura NUMA (acceso de memoria no uniforme), entonces usted deseará considerar una PLE para cada nodo separadamente, lo cual puede ser hecho con la siguiente consulta:

SELECT*FROM sys.dm_os_performance_countersWHERE dm_os_performance_counters.object_name LIKE '%Buffer Node%'AND dm_os_performance_counters.counter_name = 'Page life expectancy';

En un servidor sin NUMA, estos valores serán idénticos. En un servidor con una arquitectura NUMA, habrá múltiples filas PLE retornadas, todas ellas se sumarán al total dado para el administrador del búfer como un todo. Si usted está trabajando con NUMA, asegúrese de considerar PLE en cada nodo, en adición al total, ya que es posible que un nodo sea un cuello de botella, mientras que el total general se ve aceptable.

La pregunta más obvia ahora es, “¿Cuál es un buen valor para la PLE?” Para responder esta pregunta, necesitamos revisar más profundamente en el servidor para ver cuánta memoria tiene, y cuál debería ser el volumen esperado de datos siendo escritos o leídos. 300 segundos es a menudo citado como un buen valor para la PLE, pero como muchas respuestas fáciles y rápidas, esta seguramente es incorrecta.

Antes de considerar cómo debería verse la PLE, consideremos un poco más lo que significa. Consideremos un servidor que tiene 256GB de RAM, de lo cual 192GB están asignados a SQL Server en su configuración. Reviso la vista dm_os_sys_info y encuentro que actualmente hay cercan de 163GB enviados a la caché del búfer. Finalmente, reviso el contador de desempeño arriba y encuentro que la PLE en este servidor es 2000 segundos.

Basado en estas métricas, podemos saber que tenemos 163GB de memoria disponible para la caché del búfer, y los datos existirán ahí por cerca de 2000 segundos. Esto significa que estamos leyendo, en promedio, 163GB por cada 2000 segundos, lo que resulta ser aproximadamente 83MB/segundo. Este número es muy útil, ya que nos da un indicador claro de cuán activamente está siendo accedido nuestro SQL Server por aplicaciones o procesos. Antes de considerar qué es un buen PLE, necesitamos preguntarnos a nosotros mismos algunas cosas:

¿Cuánto tráfico esperamos en promedio por nuestras aplicaciones/servicios?

¿Hay momentos “especiales” cuando los respaldos, el mantenimiento de índices, el archivado, DBCC CheckDB, u otros procesos puedan causar que la PLE se vuelva muy lenta?

¿Es la latencia un problema? ¿Hay esperas medibles que están causando que las aplicaciones se desempeñen pobremente?

¿Hay esperas IO significativas en el servidor?

¿Qué consultas esperamos que lean la mayor parte de los datos?

En otras palabras, ¡conozca sus datos! La única respuesta verdadera a la pregunta de la PLE es que un valor bueno de PLE es uno que representa el desempeño óptimo del servidor con suficiente espacio libre para responder por el crecimiento y los picos de uso. Por ejemplo, tomemos el servidor de más antes, el cual tiene 163GB de memoria dedicada a la caché del búfer, una PLE promedio de 2000 segundos y un rendimiento extrapolado de 83MB/segundo. Después de algo de investigación adicional, descubrí que el desempeño comienza a sufrir cuando la PLE cae por debajo de 1500 segundos. Desde este punto, yo hago dilige -->

De forma similar, podemos dividir los datos por índice en lugar de por tabla, proveyendo incluso más granularidad en el uso de la caché del búfer:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idINNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY COUNT(*) DESC;

Esta consulta es casi la misma que nuestra última, excepto que hacemos una combinación adicional a sys.indexes, y agrupamos en el nombre del índice en adición al nombre de la tabla/vista. Los resultados proveen incluso más detalles acerca de cómo la caché del búfer está siendo usada, y pueden ser valiosos en tablas con muchos índices de uso variado:

Los resultados pueden ser útiles cuando se trata de determinar el nivel general de uso para un índice específico en cualquier momento. Adicionalmente, nos permite estimar cuánto de un índice está siendo leído, comparado con su tamaño general.

Para recolectar el porcentaje de cada tabla que está en la memoria, podemos poner esa consulta en un CTE y comparar las páginas en memoria versus el total para cada tabla:

WITH CTE_BUFFER_CACHE AS (SELECTobjects.name AS object_name,objects.type_desc AS object_type_description,objects.object_id,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MBFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY objects.name, objects.type_desc, objects.object_id)SELECTPARTITION_STATS.name,CTE_BUFFER_CACHE.object_type_description,CTE_BUFFER_CACHE.buffer_cache_pages,CTE_BUFFER_CACHE.buffer_cache_used_MB,PARTITION_STATS.total_number_of_used_pages,PARTITION_STATS.total_number_of_used_pages * 8 / 1024 AS total_mb_used_by_object,CAST((CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memoryFROM CTE_BUFFER_CACHEINNER JOIN (SELECT objects.name,objects.object_id,SUM(used_page_count) AS total_number_of_used_pages

FROM sys.dm_db_partition_statsINNER JOIN sys.objectsON objects.object_id = dm_db_partition_stats.object_idWHERE objects.is_ms_shipped = 0GROUP BY objects.name, objects.object_id) PARTITION_STATSON PARTITION_STATS.object_id = CTE_BUFFER_CACHE.object_idORDER BY CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) DESC;

Esta consulta combina nuestro conjunto previo de datos con una consulta en sys.dm_db_partition_stats para comparar lo que está actualmente en la caché del búfer versus el espacio total usado por cualquier tabla dada. Las muchas operaciones CAST al final ayudan a evitar el truncado y hacen al resultado final fácil de leer. Los resultados en mi servidor local son los siguientes:

Estos datos nos dicen qué tablas están las zonas calientes en nuestra base de datos, y con un poco de conocimiento de su uso de aplicación, podemos determinar cuáles simplemente tienen demasiados datos residiendo en la memoria. Las tablas pequeñas son probablemente no muy importantes para nosotros aquí. Por ejemplo, las primeras cuatro en la salida anterior están debajo de un megabyte y si quisiéramos omitirlas, podríamos filtrar los resultados para retornar solamente tablas más grandes que un tamaño específico de interés.

Por otra parte, estos datos nos dicen que ¾ de SalesOrderDetail está en la caché del búfer. Si esto pareciera inusual, consultaría la caché del plan de consultas y determinaría si hay alguna consulta ineficiente en la tabla que está seleccionando *, o una cantidad excesivamente grande de datos. Combinando nuestras métricas desde la caché del búfer y la caché del plan, podemos idear nuevas maneras de determinar con precisión mañas consultas o aplicaciones que están jalando muchos más datos de lo que requieren.

Esta consulta puede ser modificada para proveer el porcentaje de un índice que está siendo usado también, de forma similar a cómo recolectamos el porcentaje de una tabla usada:

SELECTindexes.name AS index_name,objects.name AS object_name,objects.type_desc AS object_type_description,COUNT(*) AS buffer_cache_pages,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB,

SUM(allocation_units.used_pages) AS pages_in_index,SUM(allocation_units.used_pages) * 8 /1024 AS total_index_size_MB,CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memoryFROM sys.dm_os_buffer_descriptorsINNER JOIN sys.allocation_unitsON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_idINNER JOIN sys.partitionsON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))INNER JOIN sys.objectsON partitions.object_id = objects.object_idINNER JOIN sys.indexesON objects.object_id = indexes.object_idAND partitions.index_id = indexes.index_idWHERE allocation_units.type IN (1,2,3)AND objects.is_ms_shipped = 0AND dm_os_buffer_descriptors.database_id = DB_ID()GROUP BY indexes.name, objects.name, objects.type_descORDER BY CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) DESC;